IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CreateProcedurePrices]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[CreateProcedurePrices]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
/***************************************************************************
* Name:			CreateProcedurePrices
* Purpose:		Create's the procedure prices.
* 
* PARAMETERS
* Name				Description					
* -------------		-----------------------------------------------------------
*
* ERRORS USED		Description
* -------------		-----------------------------------------------------
* n/a
*
* RETURN VALUE
* Value    			Description					
* --------------	------------------------------------------------------
* @_exitStatus		An exit status of either true or false
***************************************************************************/
Create Procedure dbo.[CreateProcedurePrices]
AS
Begin

set nocount on

Truncate Table dbo.[ProcedurePickerPrices]

If Exists(select 1 from sysindexes where name = 'IDX_ProcedurePickerPrices_ChargeCode') drop index dbo.ProcedurePickerPrices.IDX_ProcedurePickerPrices_ChargeCode;
If Exists(select 1 from sysindexes where name = 'IDX_ProcedurePickerPrices_Description') drop index dbo.ProcedurePickerPrices.IDX_ProcedurePickerPrices_Description;
If Exists(select 1 from sysindexes where name = 'IDX_ProcedurePickerPrices_CPTCode') drop index dbo.ProcedurePickerPrices.IDX_ProcedurePickerPrices_CPTCode;
If Exists(select 1 from sysindexes where name = 'IDX_ProcedurePickerPrices_Location')  drop index dbo.ProcedurePickerPrices.IDX_ProcedurePickerPrices_Location;

--This table will hold the selected CPTcode/Dept combinations to join to.

if object_id('tempdb..#CPTtable') is not null drop table #CPTtable
Create table #CPTtable(Dept varchar(10) null,CptCode varchar(10) null);

	/*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
						M G H  P R I C E  S E L E C T O R
	CPT Table variable will be loaded with all combinations of CPTcode / Dept
	that exist in both the CHARGEMASTER table and the MGH_CDMFEED table 
	according to the selection criteria provided.  This will be used to
	select Tech, Pro and GLobal fees.
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/

	Insert into #CPTtable(Dept,CptCode)
		Select Distinct [Dept],[CPTCode] From [dbo].[mgh_cdmfeed] a

	/*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
						B W H  P R I C E  S E L E C T O R
	CPT Table variable will be loaded with all combinations of CPTcode / Dept
	that exist in both the CHARGEMASTER table and the BWH_CDMFEED table 
	according to the selection criteria provided.  This will be used to
	select Tech, Pro and GLobal fees.
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/

	Insert into #CPTtable(Dept,CptCode)
		Select distinct [Dept],[CPTCode] From [dbo].[bwh_cdmfeed] a

	Create Index DeptIndex on #CPTtable (dept,cptcode)
	Create Index CPTCodeIndex on #CPTtable (cptcode,dept)

	/*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
						M G H  P R I C E  R E S U L T S E T
	CPT Table variable is used to join the MGH_CDMFEED and CHARGEMASTER tables
	on CPTcode and department.  The resultset will contain a single row for
	each matching CPTcode, Department, Description, Location, and ChargeCode.
	Depending on an available Tech/Pro/Global fee row, each column will
	contain data from the Tech row or if not there, the pro-fee row, or if
	not there, either blank or the chargemaster row.
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/

	Insert Into ProcedurePickerPrices(
		Institution
		,Dept
		,Description
		,Price
		,ChargeCode
		,CPTCode
		,ResearchRate
		,GovernmentRate
		,IndustryRate
		,ResearchCharge
		,GovernmentCharge
		,IndustryCharge
		,GlobalFee
		,NFGlobalFee
		,ProFee
		,POTechFee
		,NFPOTechFee
		,POProFee
		,NFPOProFee
		,Location
	)
	Select Distinct 'MGH' as 'Institution',
	   coalesce(t.[Dept],p.[Dept],'') as 'Dept'
	  ,coalesce(t.[Description],p.[Description],gl.[Description],'') as 'Description'
	  ,IsNull(t.[Price],0) as 'Price'
	  ,coalesce(t.[ChargeCode],p.[ChargeCode],0) as 'ChargeCode'
	  ,c.[CPTCode] as 'CPTCode'
	  ,IsNull(t.[ResearchRate],0)
	  ,0.00 as 'GovernmentRate'
	  ,0.00 as 'IndustryRate'
	  ,case when IsNull(t.[ResearchRate],0)<>0 then 
				Round(IsNull(((IsNull(t.[Price],0) * IsNull(t.[ResearchRate],0))/100), 0) ,2)
			else IsNull(t.[Price],0) 
		end as 'ResearchCharge'
	  ,0.00 as 'GovernmentCharge'
	  ,0.00 as 'IndustryCharge'
	  ,IsNull(gl.Price,0) as 'GlobalFee'
	  ,IsNull(gl.NFPrice,0) as 'NFGlobalFee'
	  ,IsNull(p.[Price],0) as 'ProFee'
	  ,IsNull(ct.Price,0) as 'POTechFee'
	  ,IsNull(ct.NFPrice,0) as 'NFPOTechFee'
	  ,Case when IsNull(gl.Price,0)<>0 and cp.cptcode is null and ct.cptcode is null then 
				IsNull(gl.Price,0)
			Else IsNull(cp.Price,0) 
		End as 'POProFee'
	  ,Case when IsNull(gl.NFPrice,0)<>0 and cp.cptcode is null and ct.cptcode is null then 
				IsNull(gl.NFPrice,0)
			Else IsNull(cp.NFPrice,0) 
		End as 'NFPOProFee'
	  ,coalesce(t.[Location],p.[Location],'') as 'Location'
	  From #CPTtable c
	  left outer join [dbo].[mgh_cdmfeed] t on c.cptcode = t.cptcode and c.dept = t.dept and IsNull(c.Dept,'')<>''
												and left(t.[techProCode],1)='T'
	  Left outer Join [dbo].[mgh_cdmfeed] p on p.cptcode = c.cptcode and p.dept =c.dept  and IsNull(c.Dept,'')<>''
												and left(p.[TechProCode],1) = 'P'
	  Left outer Join [dbo].[ChargeMaster] gl on gl.CPTCode = c.cptcode 
	  Left outer Join [dbo].[ChargeMaster] cp on cp.CPTCode = c.cptcode+'26' 
	  Left outer Join [dbo].[ChargeMaster] ct on ct.CPTCode = c.cptcode+'TC' 
	  where coalesce(t.[ChargeCode],p.[ChargeCode],0)<>0
	


	/*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
						B W H  P R I C E  R E S U L T S E T
	CPT Table variable is used to join the BWH_CDMFEED and CHARGEMASTER tables
	on CPTcode and department.  The resultset will contain a single row for
	each matching CPTcode, Department, Description, Location, and ChargeCode.
	Depending on an available Tech/Pro/Global fee row, each column will
	contain data from the Tech row or if not there, the pro-fee row, or if
	not there, either blank or the chargemaster row.
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/


	Insert Into ProcedurePickerPrices(
		Institution
		,Dept
		,Description
		,Price
		,ChargeCode
		,CPTCode
		,ResearchRate
		,GovernmentRate
		,IndustryRate
		,ResearchCharge
		,GovernmentCharge
		,IndustryCharge
		,GlobalFee
		,NFGlobalFee
		,ProFee
		,POTechFee
		,NFPOTechFee
		,POProFee
		,NFPOProFee
		,Location
	)
	Select Distinct 'BWH',
	   coalesce(t.[Dept],p.[Dept],'') as 'Dept'
	  ,coalesce(t.[Description],p.[Description],gl.[Description],'') as 'Description'
	  ,IsNull(t.[Price],0) as 'Price'
	  ,coalesce(t.[ChargeCode],p.[ChargeCode],0) as 'ChargeCode'
	  ,c.[CPTCode] as 'CPTCode'
	  ,0 as 'ResearchRate'
	  ,IsNull(cast(t.[GovernmentRate] as decimal(14,10)),0) as 'GovernmentRate'
	  ,IsNull(cast(t.[IndustryRate] as decimal(14,10)),0) as 'IndustryRate'
	  ,0 as 'ResearchCharge'
	  ,case when IsNull(cast(t.[GovernmentRate] as decimal(14,10)),0) <> 0 then 
				Round(IsNull(cast(t.[GovernmentRate] as decimal(14,10)) * t.[Price], 0),2)
			else IsNull(t.[Price],0)
		end as 'GovernmentCharge'
	  ,case when IsNull(cast(t.[IndustryRate] as decimal(14,10)),0) <> 0 then 
				Round(IsNull(cast(t.[IndustryRate] as decimal(14,10)) * t.[Price], 0),2)
			else IsNull(t.[Price],0)
		end as 'IndustryCharge'
	  ,IsNull(gl.Price,0) as 'GlobalFee'
	  ,IsNull(gl.NFPrice,0) as 'NFGlobalFee'
	  ,IsNull(p.[Price],0) as 'ProFee'
	  ,IsNull(ct.Price,0) as 'POTechFee'
	  ,IsNull(ct.NFPrice,0) as 'NFPOTechFee'
	  ,Case when IsNull(gl.Price,0)<>0 and cp.cptcode is null and ct.cptcode is null then 
				IsNull(gl.Price,0)
			Else IsNull(cp.Price,0) 
		End as 'POProFee'
	  ,Case when IsNull(gl.NFPrice,0)<>0 and cp.cptcode is null and ct.cptcode is null then 
				IsNull(gl.NFPrice,0)
			Else IsNull(cp.NFPrice,0) 
		End as 'NFPOProFee'
	  ,coalesce(t.[Location],p.[Location],'') as 'Location'
	  From #CPTtable c
	  left outer join [dbo].[bwh_cdmfeed] t on c.cptcode = t.cptcode and c.dept = t.dept and IsNull(c.Dept,'')<>''
												and left(t.[UserStatus],1)='T'
	  Left outer Join [dbo].[bwh_cdmfeed] p on p.cptcode = c.cptcode and p.dept =c.dept  and IsNull(c.Dept,'')<>''
												and left(p.[UserStatus],1) = 'P'
	  Left outer Join [dbo].[ChargeMaster] gl on gl.CPTCode = c.cptcode 
	  Left outer Join [dbo].[ChargeMaster] cp on cp.CPTCode = c.cptcode+'26' 
	  Left outer Join [dbo].[ChargeMaster] ct on ct.CPTCode = c.cptcode+'TC'
	  where coalesce(t.[ChargeCode],p.[ChargeCode],0)<>0


	/*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
						B W H  P O  P R I C E  R E S U L T S E T
	Get the CHARGEMASTER table for each CPTcode that does not exist in the 
	BWH_CDMFEED table.
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/

	Insert Into ProcedurePickerPrices(
		Institution
		,Dept
		,Description
		,Price
		,ChargeCode
		,CPTCode
		,ResearchRate
		,GovernmentRate
		,IndustryRate
		,ResearchCharge
		,GovernmentCharge
		,IndustryCharge
		,GlobalFee
		,NFGlobalFee
		,ProFee
		,POTechFee
		,NFPOTechFee
		,POProFee
		,NFPOProFee
		,Location
	)
	Select Distinct 'BWH',
	   'BWPO' as 'Dept'
	  ,coalesce(c.[Description],'') as 'Description'
	  ,null as 'Price'
	  ,c.[Id]*-1 as 'ChargeCode'
	  ,c.[CPTCode] as 'CPTCode'
	  ,null as 'ResearchRate'
	  ,null as 'GovernmentRate'
	  ,null as 'IndustryRate'
	  ,null as 'ResearchCharge'
	  ,null as 'GovernmentCharge'
	  ,null as 'IndustryCharge'
	  ,IsNull(c.Price,0) as 'GlobalFee'
	  ,IsNull(c.NFPrice,0) as 'NFGlobalFee'
	  ,null as 'ProFee'
	  ,IsNull(ct.Price,0) as 'POTechFee'
	  ,IsNull(ct.NFPrice,0) as 'NFPOTechFee'
	  ,Case when IsNull(c.Price,0)<>0 and cp.cptcode is null and ct.cptcode is null then 
				IsNull(c.Price,0)
			Else IsNull(cp.Price,0) 
		End as 'POProFee'
	  ,Case when IsNull(c.NFPrice,0)<>0 and cp.cptcode is null and ct.cptcode is null then 
				IsNull(c.NFPrice,0)
			Else IsNull(cp.NFPrice,0) 
		End as 'NFPOProFee'
	  ,'BWPO' as 'Location'
	  From [dbo].[ChargeMaster] c
	  Left outer Join [dbo].[ChargeMaster] cp on cp.CPTCode = c.cptcode+'26' 
	  Left outer Join [dbo].[ChargeMaster] ct on ct.CPTCode = c.cptcode+'TC'
	  where len(c.CPTCode)=5
			and not exists(select 1 from [dbo].[bwh_cdmfeed] t where c.cptcode = t.cptcode)


	/*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
						M G H  P O  P R I C E  R E S U L T S E T
	Get the CHARGEMASTER table for each CPTcode that does not exist in the 
	MGH_CDMFEED table.
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/

	Insert Into ProcedurePickerPrices(
		Institution
		,Dept
		,Description
		,Price
		,ChargeCode
		,CPTCode
		,ResearchRate
		,GovernmentRate
		,IndustryRate
		,ResearchCharge
		,GovernmentCharge
		,IndustryCharge
		,GlobalFee
		,NFGlobalFee
		,ProFee
		,POTechFee
		,NFPOTechFee
		,POProFee
		,NFPOProFee
		,Location
	)
	Select Distinct 'MGH',
	   'MGPO' as 'Dept'
	  ,coalesce(c.[Description],'') as 'Description'
	  ,null as 'Price'
	  ,c.[Id]*-1 as 'ChargeCode'
	  ,c.[CPTCode] as 'CPTCode'
	  ,null as 'ResearchRate'
	  ,null as 'GovernmentRate'
	  ,null as 'IndustryRate'
	  ,null as 'ResearchCharge'
	  ,null as 'GovernmentCharge'
	  ,null as 'IndustryCharge'
	  ,IsNull(c.Price,0) as 'GlobalFee'
	  ,IsNull(c.NFPrice,0) as 'NFGlobalFee'
	  ,null as 'ProFee'
	  ,IsNull(ct.Price,0) as 'POTechFee'
	  ,IsNull(ct.NFPrice,0) as 'NFPOTechFee'
	  ,Case when IsNull(c.Price,0)<>0 and cp.cptcode is null and ct.cptcode is null then 
				IsNull(c.Price,0)
			Else IsNull(cp.Price,0) 
		End as 'POProFee'
	  ,Case when IsNull(c.NFPrice,0)<>0 and cp.cptcode is null and ct.cptcode is null then 
				IsNull(c.NFPrice,0)
			Else IsNull(cp.NFPrice,0) 
		End as 'NFPOProFee'
	  ,'MGPO' as 'Location'

	  From [dbo].[ChargeMaster] c
	  Left outer Join [dbo].[ChargeMaster] cp on cp.CPTCode = c.cptcode+'26' 
	  Left outer Join [dbo].[ChargeMaster] ct on ct.CPTCode = c.cptcode+'TC'
	  where len(c.CPTCode)=5
			and not exists(select 1 from [dbo].[mgh_cdmfeed] t where c.cptcode = t.cptcode)

Create Index IDX_ProcedurePickerPrices_ChargeCode on dbo.ProcedurePickerPrices(ChargeCode);
Create Index IDX_ProcedurePickerPrices_Description on dbo.ProcedurePickerPrices(Description);
Create Index IDX_ProcedurePickerPrices_CPTCode on dbo.ProcedurePickerPrices(CPTCode,Dept);
Create Index IDX_ProcedurePickerPrices_Location on dbo.ProcedurePickerPrices(Location);

END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
